﻿using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Web;

using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Services.Client;
using System.Reflection;

namespace Odp.DataServices
{
	public class sqlServerConnection
	{
		private SqlConnection connection = null;

		public sqlServerConnection(string connStr)
		{
            connection = new SqlConnection(connStr);
		}

		public void Open()
		{
			connection.Open();
		}

		public int Version
		{
			get
			{
				var reader = GetReader("SELECT SERVERPROPERTY('productversion') AS version, SERVERPROPERTY ('productlevel') AS level, SERVERPROPERTY ('edition') AS edition");
				if(!reader.Read())
					throw new Exception("Could not determine SQL server version.");
				string version = reader.GetString(0);
				string[] versionparts = version.Split('.');
				int ver = Convert.ToInt32(versionparts[0]);
				return ver;
			}
		}
		
		//TODO CHANGE, this is not the way to get a connection to SQL server but it is the fastest to get some work done quickly
		public static sqlServerConnection GetDataServiceInstance()
		{
			sqlServerConnection sql=new sqlServerConnection(sqlAppSettings.DataServiceConnectionString);
			sql.Open();

			return sql;
		}

		//TODO CHANGE, this is not the way to get a connection to SQL server but it is the fastest to get some work done quickly
		public static sqlServerConnection GetAppMetadataInstance()
		{
			sqlServerConnection sql=new sqlServerConnection(sqlAppSettings.AppMetadataConnectionString);
			sql.Open();

			return sql;
		}

		//conversion between SQL server and edm. data types (entity data model)
		private string sqlToEdm(string sql)
		{
			//see http://www.odata.org/developers/protocols/overview for refence
			var ct = new string[]{"char","varchar","text","nchar","nvarchar","ntext"};
			var it = new string[]{"int","smallint","tinyint"};
			var nt = new string[]{"decimal","numeric"};
			var dt = new string[]{"date","datetime2","smalldatetime","datetime","time"};
			var bt = new string[]{"binary","varbinary","image"};

			if (ct.Any(s => s.Equals(sql)))
				return "Edm.String";
			else
			if (it.Any(s => s.Equals(sql)))
				return "Edm.Int32";
			else
			if(sql == "bigint")
				return "Edm.Int64";
			else
			if (sql == "float")
				return "Edm.Double";
			else
			if (sql == "real")
				return "Edm.Single";
			else
				if (nt.Any(s => s.Equals(sql)))
				return "Edm.Decimal";
			else
			if (dt.Any(s => s.Equals(sql)))
				return "Edm.DateTime";
			else
			if(sql == "datetimeoffset")
				return "Edm.DateTimeOffset";
			else
			if (bt.Any(s => s.Equals(sql)))
				return "Edm.Binary";
			else
			if(sql == "uniqueidentifier")
				return "Edm.Guid";
			else
				return sql;
		}

		public static string WcfToSqlFilter(string wcf)
		{
			String where = "";
			if (!string.IsNullOrEmpty(wcf))
			{
                //and or not is OK
                where = wcf;
				where = ReplaceString(where, " Eq ", " = ", StringComparison.CurrentCultureIgnoreCase);
				where = ReplaceString(where, " Ne ", " <> ", StringComparison.CurrentCultureIgnoreCase);
				where = ReplaceString(where, " Gt ", " > ", StringComparison.CurrentCultureIgnoreCase);
				where = ReplaceString(where, " Ge ", " >= ", StringComparison.CurrentCultureIgnoreCase);
				where = ReplaceString(where, " Lt ", " < ", StringComparison.CurrentCultureIgnoreCase);
				where = ReplaceString(where, " Le ", " <= ", StringComparison.CurrentCultureIgnoreCase);
				where = ReplaceString(where, " Add ", " + ", StringComparison.CurrentCultureIgnoreCase);
				where = ReplaceString(where, " Sub ", " - ", StringComparison.CurrentCultureIgnoreCase);
				where = ReplaceString(where, " Mul ", " * ", StringComparison.CurrentCultureIgnoreCase);
				where = ReplaceString(where, " Div ", " / ", StringComparison.CurrentCultureIgnoreCase);
                //sql = ReplaceString(sql, " Mod ", "<=", StringComparison.CurrentCultureIgnoreCase);???
			}

            // Correctly format oData data types by splitting out all elements
            int i = 0;
            string[] spaceSplit = where.Split(' ');
            foreach (string item in spaceSplit)
            {
                if(item.Contains("datetime'"))
                {
                    //Take datetime'2011-06-07T13:18:25.0348565-07:00' & Convert to yyyy-mm-dd hh:mi:ss
                    DateTime dtNewDate = DateTime.Parse(item.Replace("datetime", "").Replace("'", "").Replace(")", "").Replace("(", ""));
                    string strNewDate = dtNewDate.Year + "-" + dtNewDate.Month.ToString().PadLeft(2, '0') + "-" + dtNewDate.Day.ToString().PadLeft(2, '0') + " " + dtNewDate.Hour.ToString().PadLeft(2, '0') + ":" + dtNewDate.Minute.ToString().PadLeft(2, '0') + ":" + dtNewDate.Second.ToString().PadLeft(2, '0');

                    if (item.EndsWith(")"))
                        spaceSplit[i] = String.Format("convert(datetime,'{0}',120))", strNewDate);
                    else
                        spaceSplit[i] = String.Format("convert(datetime,'{0}',120)", strNewDate);
                }
                i++;
            }

            // Recombine all elements
            where = "";
            foreach (string item in spaceSplit)
            {
                where += ' ' + item;
            }

			return where.Trim();
		}

		//this function serves interactive SDK
		//interactive SDK uses more elaborate metadata
		public SqlDataReader GetEntitySetsMetaData(string SQLquery)
		{
			string sWhere = string.IsNullOrEmpty(SQLquery)?"":" WHERE "+SQLquery;
			return GetReader(
                @"SELECT
						odpappmetadata.id, EntityId, odpcategories.Name AS Category, odpdatasources.Name AS Datasource, EntitySet, DatasetName,
						odpdatasources.Description AS DatasourceDescription, downloadlink, datasvclink, datasvckmllink, datasvckmltype,
						LastUpdateDate, ReleasedDate, ExpiredDate, ImageLink, odpappmetadata.Description
					FROM odpappmetadata
					INNER JOIN odpdatasources ON datasource_id = odpdatasources.id
					INNER JOIN odpcategories ON category_id = odpcategories.id" + sWhere+";");
		}

        //this function serves interactive SDK, gets download links for EntitySet
        //interactive SDK uses more elaborate metadata
        public SqlDataReader GetEntitySetsLinks(string SQLquery)
        {
            string sWhere = string.IsNullOrEmpty(SQLquery) ? "" : " WHERE " + SQLquery;
            return GetReader(
                @"SELECT
                    odpdownloads.id as ID, odpdownloads.entityset_id as EntitySetID, Name, Description, Link,
                    IconLink, FileType, AdditionalInfoLink, download_count as DownloadCount
                    FROM odpdownloads
                    LEFT JOIN odpanalyticinfodownloads_agg on odpdownloads.id = odpanalyticinfodownloads_agg.download_id
                    " + sWhere + ";");
        }
		
		public string ObjToString(object obj)
		{
			if(obj != null && obj.GetType() == typeof(System.DateTime))
				return ((System.DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss");
			if(obj != null && obj.GetType() != typeof(System.DBNull))
				return obj.ToString();
			else
				return "";
		}
		
		//this function serves data service
		//OData feed has only rudimentary SQL server tables/views metadata
		public sqlEntitySetMetadata sqlGetEntitySetMetaData(string entitySet)
		{
			var reader = GetReader(@"SELECT * FROM odpdataservicemetadata WHERE EntitysetName='"+entitySet+"';");
			if(reader.Read())
			{
				sqlEntitySetMetadata md = new sqlEntitySetMetadata()
					{
						EntitysetName = ObjToString(entitySet),
						TableName = ObjToString(reader["TableName"]),
						RowId = ObjToString(reader["RowId"]),
						Longitude = ObjToString(reader["Longitude"]),
						Latitude = ObjToString(reader["Latitude"]),
						KmlSnippet = ObjToString(reader["KmlSnippet"]),
						KmlName = ObjToString(reader["KmlName"]),
						KmlDescription = ObjToString(reader["KmlDescription"])
					};
				return md;
			}
			else
				throw new Exception("Entity set '"+entitySet??""+" does not exist.");
		}


		//this is SQL server specific
		//excludes columns containing KML relevant data
		public List<sqlTableMetadata> GetTablesMetadata(string entitySet, bool excludeKml)
		{
			var tables = new List<sqlTableMetadata>();

			//first get aliases
			var query = new SqlCommand(
				string.IsNullOrEmpty(entitySet)?
					"select * from odpdataservicemetadata;":
					"select * from odpdataservicemetadata where EntitysetName='"+entitySet+"';", connection);
			var entitysets = query.ExecuteReader();
			while(entitysets.Read())
			{
				var entityset = entitysets["EntitySetName"].ToString();
				var table = entitysets["TableName"].ToString();

				//TODO can we recycle this, see gskhasdfsfdsagf
				//exclude this from the OData feed
				var lon = entitysets["Longitude"];
				var lat = entitysets["Latitude"];
				var kml_ = entitysets["KmlSnippet"];
				bool longlat = lon.GetType() != typeof(System.DBNull) && lat.GetType() != typeof(System.DBNull);
				bool kmlsnip = !longlat && kml_.GetType() != typeof(System.DBNull);
				string longitude="";
				string latitude="";
				string kml="";
				if(longlat)
				{
					longitude = lon.ToString().ToLower();
					latitude = lat.ToString().ToLower();
				}
				if(kmlsnip)
					kml = kml_.ToString().ToLower();

				var props = new List<sqlPropertyMetadata>();
				var columns = new SqlCommand("EXEC sp_columns @table_name = '" + table + "'", connection);
				columns.CommandType = CommandType.Text;
				var creader = columns.ExecuteReader();
				if (creader.HasRows)
				{
					while (creader.Read())
					{
						//exclude kml from OData feed
						if(excludeKml)
						{
							if(longlat)
							{
								var col = creader["COLUMN_NAME"].ToString().ToLower();
								//if(col == longitude || col == latitude)
									//continue;//exclude
							}
							else
							if(kmlsnip)
							{
								var col = creader["COLUMN_NAME"].ToString().ToLower();
								if(col == kml)
									continue;//exclude
							}
						}

						//ok to publish the column
						props.Add(new sqlPropertyMetadata() { name = creader["COLUMN_NAME"].ToString(), dataType = sqlToEdm(creader["TYPE_NAME"].ToString()), nullable = (creader["TYPE_NAME"].ToString() == "1" ? "true" : "false") });
					}
				}
				tables.Add(new sqlTableMetadata() { name = entityset, properties = props });
			}

			return tables;
		}
		
		static public string ReplaceString(string str, string oldValue, string newValue, StringComparison comparison)
		{
			StringBuilder sb = new StringBuilder();

			int previousIndex = 0;
			int index = str.IndexOf(oldValue, comparison);
			while (index != -1)
			{
				sb.Append(str.Substring(previousIndex, index - previousIndex));
				sb.Append(newValue);
				index += oldValue.Length;

				previousIndex = index;
				index = str.IndexOf(oldValue, index, comparison);
			}
			sb.Append(str.Substring(previousIndex));

			return sb.ToString();
		}
		
		public string GetTableName(string dataSource, string entitySet)
		{
			var query = new SqlCommand("select TableName from odpdataservicemetadata WHERE EntitysetName='"+entitySet+"';", connection);
			var reader = query.ExecuteReader();
			if(reader.Read())
				return reader["TableName"].ToString();
			return null;
		}

		public SqlDataReader GetReader(string sql)
		{
			var query = new SqlCommand();
			query.Connection = connection;
			query.CommandText = sql;
			
			return query.ExecuteReader();
		}

		public int Execute(string sql)
		{
			var query = new SqlCommand();
			query.Connection = connection;
			query.CommandText = sql;
			
			return query.ExecuteNonQuery();
		}

		public SqlDataReader GetReader(string table, string columns, string where)
		{
			var query = new SqlCommand();
			query.Connection = connection;
			string sColumns = (columns=="")?"*":columns;
			string sWhere="";
			if (!string.IsNullOrEmpty(sWhere))
				sWhere = " WHERE " + sWhere;

			query.CommandText = "select "+sColumns+" from "+table+sWhere+";";
			
			return query.ExecuteReader();
		}


        public string getColumnNames(string entity)
        {
            SqlCommand sqlComm = new SqlCommand();
            string query = string.Format("SELECT * FROM {0} WHERE 1 = 0;", entity);
            string columns = string.Empty;

            sqlComm.CommandText = query;
            sqlComm.Connection = connection;
            SqlDataReader reader = sqlComm.ExecuteReader();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                columns += String.Format("{0},", reader.GetName(i));
            }

            return columns.Substring(0, columns.Length - 1);
        }

		public SqlDataReader GetReader(sqlEntitySetMetadata md, string columns, string WCFquery, int ntop, string nextRowKey, int skip, string order)
		{
			////first get the actual table
			var query = new SqlCommand();
			query.Connection = connection;
            string sColumns = string.Empty;
            string sOrder = string.Empty;
            string sWhere = WcfToSqlFilter(WCFquery); // inner where statement for the filter from the url, odprownumber clause will be in outer query

			if (!string.IsNullOrEmpty(sWhere))
				sWhere = " WHERE (" + sWhere + ")";

            if (!string.IsNullOrEmpty(columns))
                sColumns = columns;
            else
                sColumns = getColumnNames(md.TableName);

            if (!string.IsNullOrEmpty(order))
                sOrder = order;
            else
                sOrder = md.RowId;

			bool isRowId = !string.IsNullOrEmpty(md.RowId);
			if(!isRowId)
				throw new Exception("RowId not defined for entity set '"+md.EntitysetName+"'.");

            string oWhere = ""; // outer where statement for the OdpRowNumber filter
            if (skip > 0 && ntop <= 0)
                oWhere = " WHERE OdpRowNumber > " + skip.ToString();
            else if (skip <= 0 && ntop > 0)
                oWhere = " WHERE OdpRowNumber <= " + ntop.ToString();
            else if (skip > 0 && ntop > 0)
                oWhere = " WHERE OdpRowNumber BETWEEN " + (1 + skip).ToString() + " AND " + (skip + ntop).ToString();

            var sql = string.Format(
                @"WITH MyQuery AS
				(
					SELECT TOP 100 PERCENT {0}, ROW_NUMBER() OVER (ORDER BY {2}) AS OdpRowNumber
					FROM {1}
                    {3}   
				)
				SELECT	DISTINCT {0} 
				FROM	MyQuery
				{4}
                ORDER BY {5};", sColumns, md.TableName, md.RowId, sWhere, oWhere, sOrder);

			query.CommandText = sql;
            return query.ExecuteReader();
		}

		public void Close()
		{
			if (connection != null)
			{
				connection.Close();
				connection = null;
			}
		}
	}

	public class sqlEntitySetMetadata
	{
		public string EntitysetName {get;set;}
		public string TableName {get;set;}
		public string RowId {get;set;}
		public string Longitude {get;set;}
		public string Latitude {get;set;}
		public string KmlSnippet {get;set;}
		public string KmlName {get;set;}
		public string KmlDescription {get;set;}
	}
}
